# encoding: utf-8
# @author: w4dll
# @file: mytool.py
# @time: 2020/4/19 19:20

import json, sqlite3
import re
from openpyxl import load_workbook, workbook
import random
from PyQt5.Qt import *

class sqlite3Class():
    def __init__(self, db_file_path):
        self.db = db_file_path  # 存储数据库路径，便于好后面使用

    # 执行sql语句
    def excuteSql(self, str_sql):
        """
        执行sql语句
        :param str_sql: sql语句
        :return: None
        """
        con = sqlite3.connect(self.db)
        cur = con.cursor()
        try:
            cur.execute(str_sql)
            con.commit()
            print("执行成功！")
        except Exception as e:
            print(e)
            print("执行失败！")
            con.rollback()
        finally:
            cur.close()
            con.close()

    def selectdata(self, str_sql):
        """
        获取数据库中数据
        :param str_sql:str， sql语句
        :return: data
        """
        con = sqlite3.connect(self.db)
        cur = con.cursor()
        try:
            cur.execute(str_sql)
            data = cur.fetchall()
        except Exception as e:
            print(e)
            print("执行失败！")
            con.rollback()
        finally:
            cur.close()
            con.close()
            return data

    # 创建表
    def creat_table(self, str_sql):
        """
        :param str_sql: 创建表的sql语句字符串
        :return: None
        """
        con = sqlite3.connect(self.db)
        cur = con.cursor()
        try:
            cur.execute(str_sql)
            print("创建表成功！")
        except Exception as e:
            print(e)
            print("创建表失败！")
        finally:
            cur.close()
            con.close()

    # 插入单行数据
    def insert_singlerow_data(self, sql, values):
        """
        插入单行数据
        :param sql: 插入数据的sql语言
        :param values: 插入值元组
        :return: None
        """
        con = sqlite3.connect(self.db)
        cur = con.cursor()
        try:
            cur.execute(sql, values)
            con.commit()
        except Exception as e:
            print(e)
            print("插入失败！")
            con.rollback()
        finally:
            cur.close()
            con.close()

    # 获取表格所有数据
    def get_all_data(self,t_tablename):
        """
        获取数据库中指定表中所有数据
        :param t_tablename: 表名
        :return: list, 返回所有行的列表数据
        """
        sql_query = "select * from "+t_tablename
        con = sqlite3.connect(self.db)
        cur = con.cursor()
        try:
            cur.execute(sql_query)
            data = cur.fetchall()
        except Exception as e:
            print(e)
            print("获取失败！")
        finally:
            cur.close()
            con.close()
            return data

    # 获取表格所有数据
    def get_one_data(self, str_sql):
        """
        获取数据库中指定表中所有数据
        :param str_sql: sql语句
        :return: list, 返回所有行的列表数据
        """
        con = sqlite3.connect(self.db)
        cur = con.cursor()
        try:
            cur.execute(str_sql)
            data = cur.fetchone()
        except Exception as e:
            print(e)
            print("获取失败！函数->get_one_data")
        finally:
            cur.close()
            con.close()
            return data

# 动画函数
def show_out_bounce_animation(parent, str_property, point_startV, point_endV,int_duration):
    """
    窗口进入时的动画函数
    :param parent: Form，父窗口或者目标对象窗口
    :param str_property: 动画方式，pos表示位置动画
    :param point_startV: 动画开始点
    :param point_endV: 结束点
    :param int_duration: 持续时间
    :return: None
    """
    animation = QPropertyAnimation(parent)
    animation.setTargetObject(parent)
    animation.setPropertyName(str_property)
    animation.setStartValue(point_startV)
    animation.setEndValue(point_endV)
    animation.setDuration(int_duration)
    animation.setEasingCurve(QEasingCurve.OutBounce)
    animation.start(QAbstractAnimation.DeleteWhenStopped)

# 从文件中读取数据
def readjson(filepath):
    try:
        with open(filepath, "r") as file:
            data = json.load(file)
            file.close()
            return data
    except :
        return ""

# 将数据写入json文件中
def writejson(filepath, data):
    with open(filepath, "w") as file:
        json.dump(data, file)
        file.close()

def calculate_nums_vals(str_nums, str_vals, int_count):
    ls1, ls2 = str(str_nums).split(","), str(str_vals).split(",")
    sums, scores = 0, 0
    for i in range(int_count):
        sums += int(ls1[i])
        scores += int(ls1[i]) * int(ls2[i])
    return (sums, scores)

# 将excel文件转换成题库字典文件并返回
def importSubjectsFromExcelFile(file_path):
    """
    从excel文件中读取题库信息
    :param file_path: 含题库的excel文件名
    :return: list(tuple),[(...), (...)]
    """
    # a, b = re.split('[/ -.]', file_path)[-3:-1]  # 分离出单位、专业

    wb = load_workbook(file_path)   # 读入excel信息
    sheet = wb.worksheets[0]

    # 将每行数据添加到题库字典中
    step = 0
    data_tiku = []              # 存储导入的题库信息
    for row in list(sheet.rows)[1:]:  # 去掉首行标题
        val, tmp = [], ""
        for cell in row:
            val.append(cell.value)

        # 去空后存储每行数据
        s = tuple()
        for i in range(len(val)-1):
            if val[i] != None and str(val[i]).strip() != "":
                s += (val[i], )
        if val[-1] == None:
            s += ("", )
        else:
            s += (val[-1], )

        step = step + 1
        data_tiku.append(s)
    return data_tiku

# 将题库文件中的题库导入到excel表格
def writeToExcel(str_filepath, data):
    """
    将题库写入excel文件并保存
    :param str_filepath: 保存文件路径名，含文件名
    :param data: 题库中数据文件
    :return: bool，表示操作成功与否
    """
    wb = workbook.Workbook()

    # 插入表头信息
    first_row = ["题号", "适用人员", "专业分类", "题干", "题型","选项1", "选项2", "选项3", "选项4", "选项5", "选项6", "选项7", "选项8", "选项9", "选项10", "答案", "备注"]
    wb.worksheets[0].append(first_row)

    # 将每行数据写入excel表格
    for x in data:
        wb.worksheets[0].append(list(x))
    try:
        wb.save(str_filepath)
        return True
    except FileNotFoundError as e:
        print("导出题库时，excel文件保存失败！", e)
        return False

# 返回一个不重复的随机索引序列
def get_random(num, num_max):
    list_num = []
    if num >= num_max:
        return range(num_max)
    while(len(list_num) < num):
        a = random.randint(0, num_max-1)
        if a not in list_num:
            list_num.append(a)
    return list_num


if __name__ == '__main__':
    db = sqlite3Class("data.db")

    # # 创建用户信息表
    # sql = """create table t_loginstate(
    #        用户名 VARCHAR not null,
    #        密码 VARCHAR not null,
    #        是否自动登录 BOOL,
    #        是否记住密码 BOOL
    #        )"""

    # 创建题库表
    # db.creat_table(sql)
    # # 创建用户信息表
    # sql = """create table t_loginstate(
    #        用户名 VARCHAR not null,
    #        密码 VARCHAR not null,
    #        是否自动登录 BOOL,
    #        是否记住密码 BOOL
    #        )"""
    # db.creat_table(sql)
    # 插入单行
    # sql_insert = "insert into t_userinfo(用户名, 密码, 姓名) values(?, ?, ?)"
    # db.insert_singlerow_data(sql_insert, ("dll", "1", "蓝厄祖师"))

    # 修改表
    # sql = "alter table t_userinfo " \
    #       "add 注册时间 VARCHAR"
    # db.excuteSql(sql)

    # sql = "select * from t_userinfo where 用户名='dll'"
    # userinfo = db.get_one_data(sql)
    # print(userinfo[1])


